Calendar Slicers

The calendar slicer is a specialized slicer for date/ time columns coming from a SQL database, especially where the classic date/ time data groupings (quarter, semester, year, month, week) don't exist. The slicer enables you to filter the query by these groupings, without the need to construct any complex formulations. This is an excellent solution for users who are directly querying a SQL database, and cannot manipulate the database and don't want to spend time creating the relevant formulations.

Pyramid data modeling enables users to construct these hierarchical date/ time groupings when designing the ETL, and then add those elements to the query in Discover. However, this solution is only relevant to those users who construct the model in Pyramid; but many users query the database directly. In a direct querying scenario, the user may want to add 'years' to the query, while the database only contains a flat date/ time hierarchy consisting of date keys. How can this user add 'years' to the query?

Pyramid offers 2 ways solutions for this requirement: on-the-fly date part calculations, and calendar slicers. While date part calculations produce custom columns which can be added to any part of the query, the calendar slicer is used to filter the query by specified dates or time periods, date ranges, and date formulations. From the calendar slicer, the user selects the required date(s) or time period and the query is filtered on-the-fly, without creating custom columns.

Note: calendar slicers are supported only for SQL models. This functionality is not currently supported for MS OLAP, Tabular, or SAP BW.

How to Create a Calendar Slicer

A calendar slicer can be created using a date/ time column only. Add the date/ time hierarchy to the Filters zone; this produces a slicer which is added to the canvas.

When you click on the slicer, the calendar picker will be displayed instead of a drop-down list. Continue reading to learn how to navigate the calendar picker.

Navigate the Calendar Picker

Calendar Type

The calendar slicer features 3 different calendar pickers, each designed to meet a different set of user requirements.

Granularity

Rather than displaying dates only, the calendar picker allows you to specify the desired level of granularity, meaning you can choose to display any of the date time groupings in the calendar and make your selections accordingly.

Calendar Selection

The calendar picker allows selections for dates that exist in the database only. Dates and periods for which there is no data are grayed out. To make a selection from the calendar, start by selecting the required level of granularity, then select the required date or time period.

To assist in making a selection, use the back and forward arrows to move to the next or previous period, and use the up and down buttons to jump to a different month or year.

Back and Forward Buttons

Use the back and forward buttons to navigate through the current level of granularity. For example, at the date level, click the forward arrow to go to the next month, or the back arrow to go to the previous month.

At the week level, click forward to go to the next year, or back to go to the previous year.

Up and Down Buttons

Use the up and down buttons to jump to a different month or year for the current level of granularity. Up buttons indicate that you can go up a level, while down arrows indicate that you can go down a level, without making a filter selection.

Examples

To follow these examples, use the Sample Demo data model.

Multi Select

The multi select option is supported for the Simple calendar picker, allowing you to select multiple date/ time elements You can select multiple elements from different granularity levels.

Multi Select is enabled by default; to disable it, deselect its checkbox:

Quick Selection

The quick selection options provide a shortcut selection for filtering by the current, first, or last date/ time period in the current level of granularity. To male a quick selection, start by choosing the granularity level as usual. Then, instead of making a selection from the calendar, choose a quick selection.

Current: depending on the given level of granularity, selects today's date (if it exists in the system) or the current year, semester, quarter, month, or week. If the current date/ time period doesn't exist in the system, the last date/ time period will be selected instead.

First: selects the first date/ time period in the system according to the selected level of granularity. If the granularity is set to Dates, the first date in the system will be selected. If the granularity is set to months, for instance, the first month in the system will be selected.

Last: selects the last date/ time period in the system according to the selected level of granularity. If the granularity is set to Dates, the last date in the system is selected. If the granularity is set to years, the last year in the system is selected.